package team.epos.utils.dao;

import org.apache.commons.lang3.StringUtils;
import team.epos.utils.util.DbUtil;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeMap;

/**
 * Create by BaseDaoImpl on 7/13/2018
 */
public abstract class AbstractBaseDao implements BaseDao {

    @Override
    public List quaryAll(Class c) {
        String sql = "select * from " + c.getSimpleName();
        List result = new ArrayList();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DbUtil.getConnection();
        try {
            Field[] fields = c.getDeclaredFields();
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                Object obj = c.newInstance();
                for (Field f : fields) {
                    f.setAccessible(true);
                    Object o = rs.getObject(f.getName());
                    if (o instanceof BigDecimal) {
                        o = ((BigDecimal) o).intValue();
                    }
                    f.set(obj, o);
                }
                result.add(obj);
            }
        } catch (SQLException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, rs);
        }
        return result;
    }

    @Override
    public List queryWithPage(Class c, int pageIndex, int pageCount) {
        String sql = "select * from (select a.*,rownum rn from (select * from " + c.getSimpleName() + ") a where rownum <=?) where rn >= ?";
        List result = new ArrayList();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DbUtil.getConnection();
        try {
            Field[] fields = c.getDeclaredFields();
            pst = conn.prepareStatement(sql);
            pst.setInt(1, (pageIndex - 1) * pageCount + pageCount);
            pst.setInt(2, (pageIndex - 1) * pageCount + 1);
            rs = pst.executeQuery();
            while (rs.next()) {
                Object obj = c.newInstance();
                for (Field f : fields) {
                    f.setAccessible(true);
                    Object o = rs.getObject(f.getName());
                    if (o instanceof BigDecimal) {
                        o = ((BigDecimal) o).intValue();
                    }
                    f.set(obj, o);
                }
                result.add(obj);
            }
        } catch (SQLException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, rs);
        }
        return result;
    }

    @Override
    public Object getById(Class c, int id) {
        Object obj = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DbUtil.getConnection();
        Field[] fields = c.getDeclaredFields();
        try {
            StringBuilder builder = new StringBuilder();
            builder.append("select * from ");
            builder.append(c.getSimpleName());
            builder.append(" where ");
            builder.append(fields[0].getName());
            builder.append("=?");
            pst = conn.prepareStatement(builder.toString());
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                obj = c.newInstance();
                for (Field f : fields) {
                    f.setAccessible(true);
                    Object o = rs.getObject(f.getName());
                    if (o instanceof BigDecimal) {
                        o = ((BigDecimal) o).intValue();
                    }
                    f.set(obj, o);
                }
            }
        } catch (SQLException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, rs);
        }
        return obj;
    }

    @Override
    public List getByCondition(Class c, TreeMap<String, Object> param) {
        List result = new ArrayList();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DbUtil.getConnection();
        try {
            Field[] fields = c.getDeclaredFields();
            StringBuilder builder = new StringBuilder();
            builder.append("select * from ");
            builder.append(c.getSimpleName());
            builder.append(" where ");
            int len = 0;
            int paramSize = param.size();
            List<String> keyList = new ArrayList<>();
            for (String key : param.keySet()) {
                len++;
                builder.append(key + "= ? ");
                keyList.add(key);
                if (paramSize != len) {
                    builder.append(" and ");
                }
            }
            pst = conn.prepareStatement(builder.toString());
            int index = 0;
            for (String key : param.keySet()) {
                index++;
                pst.setObject(index, param.get(key));
            }
            rs = pst.executeQuery();
            while (rs.next()) {
                Object obj = c.newInstance();
                for (Field f : fields) {
                    f.setAccessible(true);
                    Object o = rs.getObject(f.getName());
                    if (o instanceof BigDecimal) {
                        o = ((BigDecimal) o).intValue();
                    }
                    f.set(obj, o);
                }
                result.add(obj);
            }
        } catch (SQLException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, rs);
        }
        return result;
    }

    @Override
    public List getByConditionWithPage(Class c, TreeMap<String, Object> param, int pageIndex, int pageCount) {
        List result = new ArrayList();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DbUtil.getConnection();
        try {
            Field[] fields = c.getDeclaredFields();
            StringBuilder builder = new StringBuilder();
            builder.append("select * from (select a.*,rownum rn from (");
            builder.append("select * from ");
            builder.append(c.getSimpleName());
            builder.append(" where ");
            int len = 0;
            int paramSize = param.size();
            List<String> keyList = new ArrayList<>();
            for (String key : param.keySet()) {
                len++;
                builder.append(key + "= ? ");
                keyList.add(key);
                if (paramSize != len) {
                    builder.append(" and ");
                }
            }
            builder.append(" ) a where rownum <=?) where rn >= ?");
            pst = conn.prepareStatement(builder.toString());
            int index = 0;
            for (String key : param.keySet()) {
                index++;
                pst.setObject(index, param.get(key));
            }
            pst.setInt(++index, (pageIndex - 1) * pageCount + pageCount);
            pst.setInt(++index, (pageIndex - 1) * pageCount + 1);
            rs = pst.executeQuery();
            while (rs.next()) {
                Object obj = c.newInstance();
                for (Field f : fields) {
                    f.setAccessible(true);
                    Object o = rs.getObject(f.getName());
                    if (o instanceof BigDecimal) {
                        o = ((BigDecimal) o).intValue();
                    }
                    f.set(obj, o);
                }
                result.add(obj);
            }
        } catch (SQLException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, rs);
        }
        return result;
    }

    @Override
    public int insert(Object obj, String sequence) {
        int result = 0;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = DbUtil.getConnection();
        try {
            Class c = obj.getClass();
            Field[] fields = c.getDeclaredFields();
            StringBuilder builder = new StringBuilder();
            builder.append("insert into ");
            builder.append(c.getSimpleName());
            builder.append("(");
            for (int i = 0; i < fields.length; i++) {
                builder.append(fields[i].getName());
                if (i != fields.length - 1) {
                    builder.append(" , ");
                }
            }
            builder.append(") ");
            builder.append("values(");
            if (StringUtils.isNotBlank(sequence)) {
                builder.append(sequence);
                builder.append(".nextVal");
                builder.append(",");
            }
            for (int i = 1; i < fields.length; i++) {
                builder.append(" ? ");
                if (i != fields.length - 1) {
                    builder.append(" , ");
                }
            }
            builder.append(")");
            pst = conn.prepareStatement(builder.toString(), new String[]{fields[0].getName()});
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                pst.setObject(i, fields[i].get(obj));
            }
            pst.executeUpdate();
            rs = pst.getGeneratedKeys();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, rs);
        }
        return result;
    }

    @Override
    public int update(Object obj) {
        int result = 0;
        PreparedStatement pst = null;
        Connection conn = DbUtil.getConnection();
        try {
            Class c = obj.getClass();
            Field[] fields = c.getDeclaredFields();
            StringBuilder builder = new StringBuilder();
            builder.append("update ");
            builder.append(c.getSimpleName());
            builder.append(" set ");
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                builder.append(fields[i].getName());
                builder.append(" = ? ");
                if (i != fields.length - 1) {
                    builder.append(" , ");
                }
            }
            builder.append(" where ");
            builder.append(fields[0].getName());
            builder.append("=?");
            pst = conn.prepareStatement(builder.toString());
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                pst.setObject(i, fields[i].get(obj));
            }
            fields[0].setAccessible(true);
            pst.setObject(fields.length, fields[0].get(obj));
            result = pst.executeUpdate();
        } catch (SQLException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, null);
        }
        return result;
    }

    @Override
    public int updateByCondition(Object obj) {
        int result = 0;
        PreparedStatement pst = null;
        Connection conn = DbUtil.getConnection();
        try {
            Class c = obj.getClass();
            Field[] fields = c.getDeclaredFields();
            List<Field> fieldList = new ArrayList<>();
            for (int i = 1; i < fields.length; i++) {
                fields[i].setAccessible(true);
                if (fields[i].get(obj) != null) {
                    fieldList.add(fields[i]);
                }
            }
            StringBuilder builder = new StringBuilder();
            builder.append("update ");
            builder.append(c.getSimpleName());
            builder.append(" set ");
            for (int i = 0; i < fieldList.size(); i++) {
                Field f = fieldList.get(i);
                f.setAccessible(true);
                builder.append(f.getName());
                builder.append(" = ? ");
                if (i != fieldList.size() - 1) {
                    builder.append(" , ");
                }
            }
            builder.append(" where ");
            builder.append(fields[0].getName());
            builder.append("=?");
            pst = conn.prepareStatement(builder.toString());
            int index = 0;
            for (Field f : fieldList) {
                index++;
                pst.setObject(index, f.get(obj));
            }
            fields[0].setAccessible(true);
            pst.setObject(index + 1, fields[0].get(obj));
            result = pst.executeUpdate();
        } catch (SQLException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, null);
        }
        return result;
    }

    @Override
    public int deleteById(Class c, int id) {
        int result = 0;
        PreparedStatement pst = null;
        Connection conn = DbUtil.getConnection();
        try {
            Field[] fields = c.getDeclaredFields();
            StringBuilder builder = new StringBuilder();
            builder.append("delete from ");
            builder.append(c.getSimpleName());
            builder.append(" where ");
            builder.append(fields[0].getName());
            builder.append(" =? ");
            pst = conn.prepareStatement(builder.toString());
            pst.setObject(1, id);
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, null);
        }
        return result;
    }

    @Override
    public int deleteByCondition(Class c, TreeMap<String, Object> param) {
        int result = 0;
        PreparedStatement pst = null;
        Connection conn = DbUtil.getConnection();
        try {
            StringBuilder builder = new StringBuilder();
            builder.append("delete from ");
            builder.append(c.getSimpleName());
            builder.append(" where ");
            int len = 0;
            int paramSize = param.size();
            for (String key : param.keySet()) {
                len++;
                builder.append(key + "= ? ");
                if (paramSize != len) {
                    builder.append(" and ");
                }
            }
            pst = conn.prepareStatement(builder.toString());
            int index = 0;
            for (String key : param.keySet()) {
                index++;
                pst.setObject(index, param.get(key));
            }
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn, pst, null);
        }
        return result;
    }

}
